package org.liufeng.course.service;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.liufeng.serch.entity.Gate;
import org.liufeng.serch.entity.Pond;
import org.liufeng.serch.entity.Reservoir;


/**
 * 
 * @author niux
 * @date 
 */
public class SerchService {
	private static String url="jdbc:mysql://sqld.duapp.com:4050/ToszmXBSdefwbBRfZIAt";
	private static String user="Ec2chH8MHnfQo9cUi0ga6jw9";
	private static String password="tP94jTsaYQCj8XkRw8nb3QmmYAFp4OX0";
	
	//private static String url="jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=utf-8";
	//private static String user="root";
	//private static String password="niuxiao";
	public static String returnObj(String type,String name) throws Exception{
		StringBuffer cont= new StringBuffer();
		if(type.equals("reser")){
			String trueCont= listReservoir(name).equals("")?"暂无此水库信息!":listReservoir(name);
			cont.append(trueCont);
		
		}else if(type.equals("gate")){
			String trueCont = listGate(name).equals("")?"暂无此水闸信息！":listGate(name);
			cont.append(trueCont);
		}
		else if(type.equals("pond")){
			String trueCont = listPond(name).equals("")?"暂无此海塘信息":listPond(name);
			cont.append(trueCont);
		}
		return cont.toString();
	}
	public static void main(String[] ar) throws Exception {
		System.out.print(returnObj("reser","jiufengshan"));
		//System.out.print(returnObj("gate","横江闸"));
		//System.out.print(returnObj("pond","松宁塘"));
	}
	
	
	private static String listReservoir(String name) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		//获得Connection对象
		Connection DBConn = DriverManager.getConnection(url,user,password);
		PreparedStatement ps=null;
		String sql = "select d.adminUnit,d.adminUnitOfficerName,d.adminUniterOfficerDuty,t.* from reservoir t left join dutyofficers d on t.id=d.reserId where t.name=? or t.pinyin like ?";
		ResultSet rs = null;
		StringBuffer cont= new StringBuffer();
		try {
			ps = DBConn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setString(2, "%"+Trans2PinYin.trans2PinYin(name)+"%");
			rs = ps.executeQuery();
			while(rs.next()){
				cont.append("水库名称：").append(rs.getString("name")).append("\n");
				cont.append("所属乡镇：").append(rs.getString("town")).append("\n");
				cont.append("所在位置：").append(rs.getString("villageName")).append("\n");
				cont.append("集雨面积：").append(rs.getDouble("square")).append("km²\n");
				cont.append("总  库  容：").append(rs.getDouble("totleCapacity")).append("万m³\n");
				cont.append("正常库容：").append(rs.getDouble("nomalCapacity")).append("万m³\n");
				cont.append("水库等级：").append(rs.getString("flag")).append("型\n");
				cont.append("最大坝高：").append(rs.getDouble("damHight")).append("m\n");
				cont.append("坝顶长度：").append(rs.getDouble("damDingHight")).append("m\n");
				cont.append("坝顶宽度：").append(rs.getDouble("damDingWidth")).append("m\n");
				cont.append("正常水位：").append(rs.getDouble("underHight")).append("m\n");
				//cont.append("当前蓄水位：").append(rs.getDouble("underHight")).append("m\n");
				if(rs.getString("stcd")!=null&&!rs.getString("stcd").equals("null")&&!rs.getString("stcd").equals("")){
					Map map = RequestService.getSFXX(rs.getString("stcd"),"reser");
					if(map.get("flag").equals("T")){
					cont.append("实时水位：").append(map.get("sssw")).append("\n");
					cont.append("实时库容：").append(map.get("sskr")).append("\n");
					cont.append("警戒水位：").append(map.get("jjsw")).append("\n");
					cont.append("蓄水比例：").append(map.get("xsbl")).append("\n");
					cont.append("是否超出警戒水位：").append(map.get("isWarn")).append("\n");
					cont.append("更新时间：").append(map.get("sTime")).append("m\n");
					}
				cont.append("管理单位：").append(rs.getDouble("adminUnit")).append("m\n");
				cont.append("责任人姓名：").append(rs.getDouble("adminUnitOfficerName")).append("m\n");
				cont.append("责任人职务：").append(rs.getDouble("adminUniterOfficerDuty"));
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBConn.close();
		}
		
		return cont.toString();
	}

	
	private static String listGate(String name) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		//获得Connection对象
		Connection DBConn = DriverManager.getConnection(url,user,password);
		PreparedStatement ps=null;
		String sql = "select * from gate where gateName=? or pinyin like ?";
		ResultSet rs = null;
		StringBuffer cont= new StringBuffer();
		try {
			ps = DBConn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setString(2, "%"+Trans2PinYin.trans2PinYin(name)+"%");
			rs = ps.executeQuery();
			while(rs.next()){
				cont.append("水闸名称：").append(rs.getString("gateName")).append("\n");
				cont.append("所在堤防：").append(rs.getString("pondName")).append("\n");
				cont.append("所属乡镇：").append(rs.getString("townCode")).append("\n");
				cont.append("主管单位：").append(rs.getString("manageLevel")).append("\n");
				cont.append("水闸规模：").append(rs.getString("scale")).append("\n");
				cont.append("过闸流量：").append(rs.getDouble("gateFlow")).append("m³\n");
				cont.append("闸门净宽：").append(rs.getDouble("totalWidth")).append("m\n");
				cont.append("闸门孔数：").append(rs.getString("holeNum")).append("孔\n");
				cont.append("闸门型式：").append(rs.getString("structureType")).append("\n");
				cont.append("主要用途：").append(rs.getString("mainUse")).append("");
				//湖头渡G潮位站数据
				Map map = RequestService.getSFXX(rs.getString("7316"),null);
				if(map.get("flag").equals("T")){
				cont.append("湖头渡G潮位站数据:");
				cont.append("实时潮位：").append(map.get("sssw")).append("\n");
				cont.append("警戒潮位：").append(map.get("jjsw")).append("\n");
				cont.append("是否超出警戒潮位：").append(map.get("isWarn"));
				cont.append("更新时间：").append(map.get("sTime")).append("\n");
				}
				
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBConn.close();
		}
		
		return cont.toString();
	}
	
	
	private static String listPond(String name) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		//获得Connection对象
		Connection DBConn = DriverManager.getConnection(url,user,password);
		PreparedStatement ps=null;
		String sql = "select * from pond where PondName=? or pinyin like ?";
		ResultSet rs = null;
		StringBuffer cont= new StringBuffer();
		try {
			ps = DBConn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setString(2, "%"+Trans2PinYin.trans2PinYin(name)+"%");
			rs = ps.executeQuery();
			while(rs.next()){
				cont.append("海塘名称：").append(rs.getString("pondName")).append("\n");
				cont.append("所属乡镇：").append(rs.getString("townCode")).append("\n");
				cont.append("主管单位：").append(rs.getString("adminUnit")).append("\n");
				cont.append("海塘长度：").append(rs.getDouble("pondLength")).append("m\n");
				cont.append("防潮标准：").append(rs.getString("moistureproof")).append("\n");
				cont.append("塘顶高程：").append(rs.getDouble("pondTopHigh")).append("m\n");
				cont.append("防浪墙高程：").append(rs.getDouble("waveScreens")).append("m\n");
				cont.append("设计高潮位：").append(rs.getDouble("highWater")).append("m\n");
				cont.append("历史最高位：").append(rs.getDouble("historyHighest")).append("m");
				
				//湖头渡G潮位站数据
	
				Map map = RequestService.getSFXX(rs.getString("7316"),null);
				if(map.get("flag").equals("T")){
				cont.append("湖头渡G潮位站数据:");
				cont.append("实时潮位：").append(map.get("sssw")).append("\n");
				cont.append("警戒潮位：").append(map.get("jjsw")).append("\n");
				cont.append("是否超出警戒潮位：").append(map.get("isWarn"));
				cont.append("更新时间：").append(map.get("sTime")).append("\n");
				}
				
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBConn.close();
		}
		
		return cont.toString();
	}
	
	
}


	
	
	
	
	
	

	
	/*
	private static List listPond(String name) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		//获得Connection对象
		Connection DBConn = DriverManager.getConnection(url,user,password);
		PreparedStatement ps=null;
		String sql = "select id,pondname,town from Pond where pondname=?";
		System.out.print(sql);
		ResultSet rs = null;
		List list = new ArrayList();
		try {
			ps = DBConn.prepareStatement(sql);
			ps.setString(1, name);
			rs = ps.executeQuery();
			while(rs.next()){
				Pond pond = new Pond();
				pond.setId(rs.getString("id"));
				pond.setPondName(rs.getString("pondname"));
				pond.setTown(rs.getString("town"));
				list.add(pond);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBConn.close();
		}
		
		return list;
	}
	

}
*/